package com.xky.ui.module.salesManagement.select;

import com.xky.pojo.SalesShipments;
import com.xky.utils.DBUtils;

import java.sql.*;
import java.time.LocalDate;

import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.swing.JOptionPane;

/**
 * 销售发货单查询工具类
 */
public class SelectSalesShipment {

    private static boolean searchMode = false;
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    
    /**
     * 重置搜索状态
     */
    public static void resetSearchStatus() {
        searchMode = false;
    }

    /**
     * 查询所有销售发货单
     * @return 销售发货单列表
     */
    public static List<SalesShipments> selectAll() {
        String sql = "SELECT * FROM sales_shipments ORDER BY shipment_id DESC";
        return executeQuery(sql, null);
    }

    /**
     * 根据发货单ID查询
     * @param id 发货单ID
     * @return 发货单
     */
    public static List<SalesShipments> selectById(Integer id) {
        searchMode = true;
        String sql = "SELECT * FROM sales_shipments WHERE shipment_id = ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setInt(1, id);
        });
    }

    /**
     * 根据发货单号查询
     * @param shipmentNumber 发货单号
     * @return 发货单列表
     */
    public static List<SalesShipments> selectByShipmentNumber(String shipmentNumber) {
        searchMode = true;
        String sql = "SELECT * FROM sales_shipments WHERE shipment_number LIKE ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setString(1, "%" + shipmentNumber + "%");
        });
    }
    
    /**
     * 根据订单ID查询
     * @param orderId 订单ID
     * @return 发货单列表
     */
    public static List<SalesShipments> selectByOrderId(Integer orderId) {
        searchMode = true;
        String sql = "SELECT * FROM sales_shipments WHERE order_id = ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setInt(1, orderId);
        });
    }
    
    /**
     * 根据状态查询
     * @param status 状态
     * @return 发货单列表
     */
    public static List<SalesShipments> selectByStatus(String status) {
        searchMode = true;
        String sql = "SELECT * FROM sales_shipments WHERE status = ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setString(1, status);
        });
    }
    
    /**
     * 关键字搜索
     * @param keyword 关键字
     * @return 发货单列表
     */
    public static List<SalesShipments> searchByKeyword(String keyword) {
        searchMode = true;
        String sql = "SELECT * FROM sales_shipments WHERE " +
                    "shipment_number LIKE ? OR " +
                    "CAST(order_id AS CHAR) LIKE ? OR " +
                    "status LIKE ? OR " +
                    "carrier LIKE ? OR " +
                    "tracking_number LIKE ? OR " +
                    "notes LIKE ? " +
                    "ORDER BY shipment_id DESC";
        
        return executeQuery(sql, preparedStatement -> {
            String param = "%" + keyword + "%";
            for (int i = 1; i <= 6; i++) {
                preparedStatement.setString(i, param);
            }
        });
    }
    
    /**
     * 根据日期范围查询
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 发货单列表
     */
    public static List<SalesShipments> selectByDateRange(LocalDate startDate, LocalDate endDate) {
        searchMode = true;
        String sql = "SELECT * FROM sales_shipments WHERE shipment_date BETWEEN ? AND ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setDate(1, java.sql.Date.valueOf(startDate));
            preparedStatement.setDate(2, java.sql.Date.valueOf(endDate));
        });
    }
    
    /**
     * 高级查询
     * @param shipmentNumber 发货单号
     * @param orderId 订单ID
     * @param status 状态
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 发货单列表
     */
    public static List<SalesShipments> advancedSearch(String shipmentNumber, Integer orderId, String status, 
                                                    LocalDate startDate, LocalDate endDate) {
        searchMode = true;
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM sales_shipments WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (shipmentNumber != null && !shipmentNumber.trim().isEmpty()) {
            sqlBuilder.append(" AND shipment_number LIKE ?");
            params.add("%" + shipmentNumber + "%");
        }

        if (orderId != null) {
            sqlBuilder.append(" AND order_id = ?");
            params.add(orderId);
        }

        if (status != null && !status.trim().isEmpty()) {
            sqlBuilder.append(" AND status = ?");
            params.add(status);
        }

        if (startDate != null) {
            sqlBuilder.append(" AND shipment_date >= ?");
            params.add(java.sql.Date.valueOf(startDate));
        }

        if (endDate != null) {
            sqlBuilder.append(" AND shipment_date <= ?");
            params.add(java.sql.Date.valueOf(endDate));
        }

        sqlBuilder.append(" ORDER BY shipment_id DESC");

        return executeQuery(sqlBuilder.toString(), preparedStatement -> {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }
        });
    }
    
    /**
     * 高级查询（使用Map条件）
     * @param conditions 查询条件Map
     * @return 发货单列表
     */
    public static List<SalesShipments> advancedSearchWithMap(Map<String, Object> conditions) {
        searchMode = true;
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM sales_shipments WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 处理发货单ID
        if (conditions.containsKey("shipmentId")) {
            sqlBuilder.append(" AND shipment_id = ?");
            params.add(Integer.parseInt(conditions.get("shipmentId").toString()));
        }
        
        // 处理发货单号
        if (conditions.containsKey("shipmentNumber")) {
            sqlBuilder.append(" AND shipment_number LIKE ?");
            params.add("%" + conditions.get("shipmentNumber") + "%");
        }

        // 处理订单ID
        if (conditions.containsKey("orderId")) {
            sqlBuilder.append(" AND order_id = ?");
            params.add(Integer.parseInt(conditions.get("orderId").toString()));
        }
        
        // 处理仓库ID
        if (conditions.containsKey("warehouseId")) {
            sqlBuilder.append(" AND warehouse_id = ?");
            params.add(Integer.parseInt(conditions.get("warehouseId").toString()));
        }

        // 处理发货日期范围
        if (conditions.containsKey("shipDateFrom")) {
            sqlBuilder.append(" AND shipment_date >= ?");
            LocalDate fromDate = LocalDate.parse(conditions.get("shipDateFrom").toString(), DATE_FORMATTER);
            params.add(java.sql.Date.valueOf(fromDate));
        }

        if (conditions.containsKey("shipDateTo")) {
            sqlBuilder.append(" AND shipment_date <= ?");
            LocalDate toDate = LocalDate.parse(conditions.get("shipDateTo").toString(), DATE_FORMATTER);
            params.add(java.sql.Date.valueOf(toDate));
        }
        
        // 处理承运商
        if (conditions.containsKey("carrier")) {
            sqlBuilder.append(" AND carrier LIKE ?");
            params.add("%" + conditions.get("carrier") + "%");
        }
        
        // 处理物流单号
        if (conditions.containsKey("trackingNumber")) {
            sqlBuilder.append(" AND tracking_number LIKE ?");
            params.add("%" + conditions.get("trackingNumber") + "%");
        }
        
        // 处理运输方式
        if (conditions.containsKey("shippingMethod")) {
            sqlBuilder.append(" AND shipping_method = ?");
            params.add(conditions.get("shippingMethod").toString());
        }
        
        // 处理状态
        if (conditions.containsKey("status")) {
            sqlBuilder.append(" AND status = ?");
            params.add(conditions.get("status").toString());
        }

        sqlBuilder.append(" ORDER BY shipment_id DESC");

        return executeQuery(sqlBuilder.toString(), preparedStatement -> {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }
        });
    }
    
    /**
     * 执行查询
     * @param sql SQL语句
     * @param paramsSetter 参数设置器
     * @return 发货单列表
     */
    private static List<SalesShipments> executeQuery(String sql, ParamsSetter paramsSetter) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        List<SalesShipments> shipmentsList = new ArrayList<>();
        
        try {
            conn = DBUtils.getConnection();
            pstmt = conn.prepareStatement(sql);
            
            if (paramsSetter != null) {
                paramsSetter.setParams(pstmt);
            }
            
            resultSet = pstmt.executeQuery();
            
            while (resultSet.next()) {
                SalesShipments shipment = new SalesShipments();
                shipment.setShipmentId(resultSet.getInt("shipment_id"));
                shipment.setShipmentNumber(resultSet.getString("shipment_number"));
                shipment.setOrderId(resultSet.getInt("order_id"));
                shipment.setWarehouseId(resultSet.getInt("warehouse_id"));
                
                // 设置日期
                Date shipmentDate = resultSet.getDate("shipment_date");
                if (shipmentDate != null) {
                    shipment.setShipmentDate(shipmentDate.toLocalDate());
                }
                
                shipment.setCarrier(resultSet.getString("carrier"));
                shipment.setTrackingNumber(resultSet.getString("tracking_number"));
                shipment.setShippingMethod(resultSet.getString("shipping_method"));
                shipment.setStatus(resultSet.getString("status"));
                shipment.setNotes(resultSet.getString("notes"));
                
                // 设置时间戳
                Timestamp createdTime = resultSet.getTimestamp("created_time");
                if (createdTime != null) {
                    shipment.setCreatedTime(createdTime.toLocalDateTime());
                }
                
                Timestamp updatedTime = resultSet.getTimestamp("updated_time");
                if (updatedTime != null) {
                    shipment.setUpdatedTime(updatedTime.toLocalDateTime());
                }
                
                shipmentsList.add(shipment);
            }
            
            return shipmentsList;
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "查询数据失败: " + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
            return new ArrayList<>();
        } finally {
            DBUtils.closeAll(conn, pstmt, resultSet);
        }
    }

    public static boolean isSearchMode() {
        return searchMode;
    }

    /**
     * 参数设置器接口
     */
    private interface ParamsSetter {
        void setParams(PreparedStatement preparedStatement) throws SQLException;
    }
} 